"""
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    created by lei.chang on '25/09/2023'
    comment: 数字货币
"""
from sqlalchemy import func, create_engine, UniqueConstraint, Index
from sqlalchemy.dialects.postgresql import INTEGER, DATE, TIMESTAMP, VARCHAR, FLOAT, NUMERIC, JSON
from sqlalchemy import Column

from project.core.base import CustomModel
from project.core.base import metadata
from project.lib.common import session_maker


class ConfigInfo(CustomModel):
    """
    """
    __tablename__ = "config_info"
    id = Column(INTEGER, primary_key=True, autoincrement=True)
    code = Column(VARCHAR(30), nullable=False, comment='编号')
    value = Column(VARCHAR(100), nullable=False, comment='值')

    __table_args__ = (
        UniqueConstraint('code', name='uq_config_info_code'),
    )


class AnnVol(CustomModel):
    """ 年化波动率表
    """
    __tablename__ = "annualized_volatility"
    id = Column(INTEGER, primary_key=True, autoincrement=True)
    date = Column(DATE, nullable=False, comment='日期')
    inst_id = Column(VARCHAR(50), nullable=False, comment='数字货币名称')
    inst_code = Column(INTEGER, nullable=False, comment='数字货币唯一代码')
    trading_ts = Column(VARCHAR(13), nullable=False, server_default='', comment='交易时间(ts) Unix时间戳的毫秒数格式，如 1597026383085')
    trading_time = Column(TIMESTAMP, comment='交易时间')
    opening_price = Column(FLOAT, nullable=False, server_default='0.0', comment='开盘价')
    highest_price = Column(FLOAT, nullable=False, server_default='0.0', comment='最高价')
    lowest_price = Column(FLOAT, nullable=False, server_default='0.0', comment='最低价')
    closing_price = Column(FLOAT, nullable=False, server_default='0.0', comment='收盘价')
    d_flu = Column(FLOAT, nullable=False, server_default='0.0', comment='每日波幅')
    fourteen_flu = Column(FLOAT, nullable=False, server_default='0.0', comment='14日波幅')
    vf = Column(FLOAT, nullable=False, server_default='0.0', comment='波动系数')
    avc = Column(FLOAT, nullable=False, server_default='0.0', comment='年化波动系数')
    atr = Column(FLOAT, nullable=False, server_default='0.0', comment='平均振幅')
    d_vol = Column(FLOAT, nullable=False, server_default='0.0', comment='每日波动率')
    ann_vol = Column(FLOAT, nullable=False, server_default='0.0', comment='年化波动率')
    trading_vol = Column(NUMERIC, nullable=False, server_default='0.0', comment='交易量 以张为单位')
    trading_vol_ccy = Column(NUMERIC, nullable=False, server_default='0.0', comment='交易量 以币为单位')
    trading_vol_quote = Column(NUMERIC, nullable=False, server_default='0.0', comment='交易量 以计价货币为单位')
    create_time = Column(TIMESTAMP, nullable=False, server_default=func.now())
    update_time = Column(TIMESTAMP, nullable=False, server_default=func.now(), onupdate=func.now())

    __table_args__ = (
        UniqueConstraint('inst_code', 'trading_ts', name='uq_annvol_inst_code'),
        Index('idx_annvol_date', 'date', 'inst_id'),
        Index('idx_annvol_inst_id', 'inst_id', 'date')
    )


class DailyKline(CustomModel):
    """ 日K线表
    """
    __tablename__ = "daily_kline"
    id = Column(INTEGER, primary_key=True)
    date = Column(DATE, nullable=False, comment='日期')
    inst_id = Column(VARCHAR(50), nullable=False, server_default='', comment='数字货币名称')
    inst_code = Column(INTEGER, nullable=False, server_default='0',  comment='数字货币唯一代码')
    trading_ts = Column(VARCHAR(13), nullable=False, server_default='', comment='交易时间(ts) Unix时间戳的毫秒数格式，如 1597026383085')
    trading_time = Column(TIMESTAMP, comment='交易时间')
    opening_price_fm = Column(FLOAT, nullable=False, server_default='0', comment='开盘价(5M)')
    highest_price_fm = Column(FLOAT, nullable=False, server_default='0',  comment='最高价(5M)')
    lowest_price_fm = Column(FLOAT, nullable=False, server_default='0',  comment='最低价(5M)')
    closing_price_fm = Column(FLOAT, nullable=False, server_default='0',  comment='收盘价(5M)')
    lowest_price_today = Column(FLOAT, nullable=False, server_default='0',  comment='最低价(今日)')
    highest_price_today = Column(FLOAT, nullable=False, server_default='0',  comment='最高价(今日)')
    lattice_negative = Column(FLOAT, nullable=False, server_default='0', comment='点阵负值')
    lattice_positive = Column(FLOAT, nullable=False, server_default='0', comment='点阵正值')
    lattice_final = Column(FLOAT, nullable=False, server_default='0', comment='点阵终值')
    create_time = Column(TIMESTAMP, nullable=False, server_default=func.now())
    update_time = Column(TIMESTAMP, nullable=False, server_default=func.now(), onupdate=func.now())

    __table_args__ = (
        UniqueConstraint('inst_code', 'trading_ts', name='uq_daily_kline_inst_code'),
        Index('idx_daily_kline_date', 'date', 'inst_id'),
        Index('idx_daily_kline_inst_id', 'inst_id', 'date')
    )


class AlgoOrder(CustomModel):
    """ 委托计划表
    """
    __tablename__ = "algo_order"
    id = Column(INTEGER, primary_key=True)
    annualized_volatility_id = Column(INTEGER, nullable=False, server_default='0', comment='年化波动率关联id')
    algo_id = Column(VARCHAR(50), nullable=False, server_default='', comment='策略委托id')
    date = Column(DATE, comment='交易日期')
    inst_id = Column(VARCHAR(50), nullable=False, server_default='', comment='数字货币名称')
    inst_code = Column(INTEGER, nullable=False, server_default='0', comment='数字货币唯一代码')
    ccy = Column(VARCHAR(30), nullable=False, server_default='', comment='保证金种 例: USDT')
    count = Column(FLOAT, nullable=False, server_default='0.0', comment='委托数量 单位: 张')
    side = Column(VARCHAR(30), nullable=False, server_default='', comment='订单方向 buy(买入)/sell(卖出)')
    pos_side = Column(VARCHAR(30), nullable=False, server_default='', comment='持仓方向 long(开多)/short(开空)')
    lever = Column(INTEGER, nullable=False, server_default='1', comment='杠杆倍数')
    trigger_price = Column(FLOAT, nullable=False, server_default='0', comment='计划委托触发价格')
    trigger_type = Column(VARCHAR(30), nullable=False, server_default='', comment='计划委托触发类型')
    trigger_ts = Column(VARCHAR(13), nullable=False, server_default='', comment='策略委托触发时间(ts) Unix时间戳的毫秒数格式，如 1597026383085')
    trigger_time = Column(TIMESTAMP, comment='策略委托触发时间')
    algo_price = Column(FLOAT, nullable=False, server_default='0.0', comment='委托价格')
    last_price = Column(FLOAT, nullable=False, server_default='0.0', comment='下单时最新成交价')
    state = Column(VARCHAR(30), nullable=False, server_default='', comment='订单状态 canceled: 撤单成功 live: 等待成交 partially_filled: 部分成交 filled: 完全成交 mmp_canceled: 做市商保护机制导致的自动撤单')
    ctime_ts = Column(VARCHAR(13), nullable=False, server_default='', comment='订单创建时间(ts) Unix时间戳的毫秒数格式，如 1597026383085')
    ctime = Column(TIMESTAMP, comment='订单创建时间')
    utime_ts = Column(VARCHAR(13), nullable=False, server_default='', comment='订单更新时间(ts) Unix时间戳的毫秒数格式，如 1597026383085')
    utime = Column(TIMESTAMP, comment='订单更新时间')
    create_time = Column(TIMESTAMP, nullable=False, server_default=func.now())
    update_time = Column(TIMESTAMP, nullable=False, server_default=func.now(), onupdate=func.now())

    __table_args__ = (
        UniqueConstraint('algo_id', name='uq_algo_order_algo_id'),
        Index('idx_algo_order_date', 'date', 'inst_id'),
        Index('idx_algo_order_inst_id', 'inst_id', 'date')
    )

    @property
    def trade_side(self):
        """
        :return:
        """
        side_str = '买入' if self.side == 'buy' else '卖出'
        pos_side_str = '开多' if self.pos_side == 'long' else '开空'

        return f'{side_str}{pos_side_str}'


class OrderLog(CustomModel):
    """ 订单日志表
    """
    __tablename__ = "order_log"
    id = Column(INTEGER, primary_key=True)
    order_type = Column(VARCHAR(50), nullable=False, server_default='', comment='订单类型: algo_order(策略委托)/close_position(平仓)')
    inst_id = Column(VARCHAR(50), nullable=False, server_default='', comment='数字货币名称')
    algo_price = Column(FLOAT, nullable=False, server_default='0.0', comment='委托价格')
    side = Column(VARCHAR(30), nullable=False, server_default='', comment='订单方向 buy(买入)/sell(卖出)')
    pos_side = Column(VARCHAR(30), nullable=False, server_default='', comment='持仓方向 long(开多)/short(开空)')
    annualized_volatility_id = Column(INTEGER, nullable=False, server_default='0', comment='年化率id')
    algo_id = Column(VARCHAR(50), nullable=False, server_default='', comment='策略委托id')
    fail_code = Column(VARCHAR(10), nullable=False, server_default='', comment='策略委托失败编号')
    fail_msg = Column(VARCHAR(500), nullable=False, server_default='', comment='策略委托失败原因')
    params = Column(JSON, nullable=True, comment='欧易平台请求参数信息')
    create_time = Column(TIMESTAMP, nullable=False, server_default=func.now())
    update_time = Column(TIMESTAMP, nullable=False, server_default=func.now(), onupdate=func.now())


def init_db(refresh=False):
    """
    :return:
    """
    DATABASE_URL = "postgresql://lane:1awC1CpqZ0OjE93W@8.210.208.94:5432/digiccy"

    engine = create_engine(DATABASE_URL, echo=True, future=True)

    # 4. 建表（不存在才创建）
    metadata.create_all(engine)


def test():

    with session_maker() as session:
        # daily_kline = DailyKline()
        # daily_kline.date = '2025-10-04',
        # daily_kline.instid = 'BTC-USDT-SWAP',
        # daily_kline.instid_code = '111111'
        #
        # session.add(daily_kline)
        # session.flush()
        # print(daily_kline.id)
        #
        # session.commit()
        rec = session.query(DailyKline).filter(DailyKline.id == 1).first()
        print(rec.dict())


if __name__ == "__main__":

    init_db(refresh=True)

